<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>
    Lost connection to MySQL server during query : Forums : PythonAnywhere
</title>
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Forums : PythonAnywhere">
        <meta name="author" content="PythonAnywhere LLP">
        <meta name="google-site-verification" content="O4UxDrfcHjC44jybs2vajc1GgRkTKCTRgVzeV6I9V14" />

        <!-- Le styles -->
        <link href="/static/bootstrap/css/bootstrap.css" rel="stylesheet">
        <link href="/static/bootstrap/css/bootstrap-responsive.css" rel="stylesheet">
        <link href="/static/anywhere/styles/bootstrap_base.css" rel="stylesheet">
        
    
    <link rel="stylesheet" href="/static/anywhere/styles/forums.css" type="text/css" media="screen" charset="utf-8" />

    <link rel="stylesheet" href="/static/pygments/style.css" type="text/css" media="screen" charset="utf-8" />

        <link rel="stylesheet" href="/static/jquery/jquery-ui-1.8.11.custom.css" type="text/css" media="screen" charset="utf-8" />
        
    
    <link rel="alternate" type="application/rss+xml" title="RSS" href="/forums/topic/774/rss" />

        <style type="text/css">
            body {
                height: auto;
            }
        </style>
        <!-- Le HTML5 shim, for IE6-8 support of HTML5 elements -->
        <!--[if lt IE 9]>
        <script src="//html5shim.googlecode.com/svn/trunk/html5.js"></script>
        <![endif]-->

        <!-- Le fav and touch icons -->
        <link rel="apple-touch-icon" href="images/apple-touch-icon.png">
        <link rel="apple-touch-icon" sizes="72x72" href="images/apple-touch-icon-72x72.png">
        <link rel="apple-touch-icon" sizes="114x114" href="images/apple-touch-icon-114x114.png">
    </head>

     <body>
        <div class="navbar navbar-fixed-top">
            <div class="navbar-inner">
                <div id="id_internal_nav_bar_container" class="container">
                    <a class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse">
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    </a>
                    <a class="brand" href="/"><img id="id_logo" src="/static/anywhere/images/logo-234x35.png" height="35" title="PythonAnywhere logo" alt="PythonAnywhere logo" /></a>
                    <div class="nav-collapse">
                        <ul id="id_header_links" class="nav">
                            <li><a id="id_feedback_link" class='feedback_link' href="">Send feedback</a></li>
                            <li><a id="id_forums_link" href="/forums/">Forums</a></li>
                            <li><a href="/wiki/" id="id_help_link">Help</a></li>
                            <li><a href="http://blog.pythonanywhere.com/" id="id_blog_link">Blog</a></li>
                            
                            
                                <li><a style="font-weight: bold;" href="/pricing/" id="id_pricing_link">Pricing & signup</a></li>
                                <li><a href="/login/?next=/forums/topic/774/" id="id_login_link">Log in</a></li>
                            
                        </ul>
                    </div>
                </div>
            </div>
        </div>

        <div class="top-container" id="id_dummy"></div>

        

        
    
    <div class="container">
        <div class="row">
            <div class="span8 offset3">
                <h1>PythonAnywhere Forums</h1>
            </div>
        </div>



    <div class="row">
        <div class="span8 offset3">
            <p>
                <a href="/forums/start_topic/" id="id_start_new_topic">Start a new topic</a> |
                <a href="/forums/">Back to all topics</a> |
                <a href="/forums/topic/774/rss">This topic's RSS</a>
            </p>
        </div>
    </div>
    <div class="row">
        <div class="span8 offset3 topic">
            <h2>Lost connection to MySQL server during query</h2>
        </div>
    </div>

    
        <div class="row">
            <div id="id_post_5492" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5492_content_text" class="span8">
                        <p>I'm trying to load data into my MySQL database and I've wrriten a script to do this. The script does 2 main things:</p>
<ol>
<li>loads data from the csv files into a set of staging tables</li>
<li>'migrates' the data from the staging tables into the operational tables</li>
</ol>
<p>The script to do this which works fine on my local web2py installation.</p>
<p>However when I run it from a PA console, the first step succeeds but then at the second step, the script exits with the error:</p>
<div class="codehilite"><pre><span class="n">_mysql_exceptions</span><span class="p">.</span><span class="n">OperationalError</span><span class="o">:</span> <span class="p">(</span><span class="mi">2013</span><span class="p">,</span> <span class="err">&#39;</span><span class="n">Lost</span> <span class="n">connection</span> <span class="n">to</span> <span class="n">MySQL</span> <span class="n">server</span> <span class="n">during</span> <span class="n">query</span><span class="err">&#39;</span><span class="p">)</span>
</pre></div>


<p>The query in question is a large one pulling back a lot of data.....is there some MySQL limit set by PA that my query is exceeding?</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5492_creator_details">
                            
                                
                                    <img src="/static/anywhere/images/star.gif" alt="Beloved premium user" title="Beloved premium user"/>
                                
                            
                            hurlingstats
                            |
                            7
                            posts
                            |
                            
                        </span>
                        <span id="id_post_5492_created_date">
                            July 21, 2013, 9:25 p.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5492">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div id="id_post_5495" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5495_content_text" class="span8">
                        <p>Had a look on StackOverflow and found the following <a href="http://stackoverflow.com/questions/1884859/lost-connection-to-mysql-server-during-query">Lost connection to MySQL server during query</a>.</p>
<p>I checked the value of <code>max_allowed_packet</code> on PA and it is set to 16M. I set my local MySQL instance to the same but could not recreate the error :(</p>
<p>Any wisdom from the crowd?</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5495_creator_details">
                            
                                
                                    <img src="/static/anywhere/images/star.gif" alt="Beloved premium user" title="Beloved premium user"/>
                                
                            
                            hurlingstats
                            |
                            7
                            posts
                            |
                            
                        </span>
                        <span id="id_post_5495_created_date">
                            July 22, 2013, 9:25 a.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5495">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div id="id_post_5501" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5501_content_text" class="span8">
                        <p>You're right that we've set <code>max_allowed_packet</code> to a very high value, so that's probably not it.  I'll do a little digging into whether there's some kind of time limit...</p>
<p>In the meantime, you could investigate re-writing your script so that it uses lots of smaller queries, rather than one big query?</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5501_creator_details">
                            
                                <img src="/static/anywhere/images/staff.png" alt="Staff" title="Staff"/>
                            
                            harry
                            |
                            376
                            posts
                            |
                            
                                PythonAnywhere staff
                                |
                            
                        </span>
                        <span id="id_post_5501_created_date">
                            July 22, 2013, 11:20 a.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5501">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div id="id_post_5506" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5506_content_text" class="span8">
                        <p>I'm not aware of any timeout in MySQL that explicitly limits the time of an actively running query, although there's nothing to stop the database administrators <a href="http://stackoverflow.com/questions/2137084/setup-mysql-query-timeout">implementing their own</a>. For connections which appear idle, the <a href="http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout"><code>wait_timeout</code></a> (or <a href="http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_interactive_timeout"><code>interactive_timeout</code></a> for interactive connections) setting can cause your connection to close. If you're pulling back a lot of data, however, I wouldn't expect the connection to have been classed as idle.</p>
<p>I guess it could also be some firewall somewhere not liking the long-running connection, but that's always going to be hard to diagnose.</p>
<p>In general I agree with Harry - if at all possible, try and use <code>WHERE</code> and <code>LIMIT</code> clauses to split your query up into manageable chunks. This often has extra benefits too, like keeping your application more responsive to input from other sources.</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5506_creator_details">
                            
                                
                                    <img src="/static/anywhere/images/star.gif" alt="Beloved premium user" title="Beloved premium user"/>
                                
                            
                            Cartroo
                            |
                            638
                            posts
                            |
                            
                        </span>
                        <span id="id_post_5506_created_date">
                            July 22, 2013, 12:55 p.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5506">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div id="id_post_5508" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5508_content_text" class="span8">
                        <p>Looking at the traceback, the 'lost connection' error occurs when my script does a <code>SELECT COUNT(*)</code> query. I wouldn't have thought such a query would have caused this error.</p>
<p>I've isolated the SQL query to a standalone script and it runs ok on PA.</p>
<p>So a bit baffled by this.</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5508_creator_details">
                            
                                
                                    <img src="/static/anywhere/images/star.gif" alt="Beloved premium user" title="Beloved premium user"/>
                                
                            
                            hurlingstats
                            |
                            7
                            posts
                            |
                            
                        </span>
                        <span id="id_post_5508_created_date">
                            July 22, 2013, 1:22 p.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5508">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div id="id_post_5510" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5510_content_text" class="span8">
                        <p>Oh, wait a sec... Does your MySQL connection go idle for some time before this query? If so, it's possible the connection is being idled out, but MySQL only actually notices that it's gone when you try and perform your next query.</p>
<p>In your script where you see the problem, you could try calling the <code>ping()</code> method on the connection object (not the cursor) just prior to performing the query - if the connection is lost, that should either automatically reconnect (problem solved) <em>or</em> it should raise an <code>OperationalError</code> in which case you'll have to create a new connection.</p>
<p>Apologies for the vagueness, but I'm dredging this up from when I implemented a MySQL connection pool in Python many moons ago and I can't quite recall whether MySQLdb has automatic reconnection enabled or not. In any case, I did discover that one should never rely on long-running transactions in MySQL - if it's going to take more than a few seconds then don't bother. If you're only reading, of course, then transactions don't really matter so things are a lot less critical.</p>
<p><strong>EDIT:</strong> The <a href="http://mysql-python.sourceforge.net/MySQLdb-1.2.2/private/_mysql.connection-class.html#ping">docstring for <code>ping()</code></a> indicates that if you call <code>conn.ping(True)</code> then it sets the automatic reconnect flag persistently for that connection, so perhaps you can just do that once when you first connect and not worry about it.</p>
<p>Just be aware that any disconnect (including due to idling) implicitly rolls back any outstanding transaction, as I mentioned earlier, which is why I tend to prefer manual reconnection because then if a transaction was pending you can raise an exception in Python so your code doesn't push the DB into an inconsistent state. Automatic reconnection seems more convenient, but if you're using transactions then it can make issues very much harder to track down.</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5510_creator_details">
                            
                                
                                    <img src="/static/anywhere/images/star.gif" alt="Beloved premium user" title="Beloved premium user"/>
                                
                            
                            Cartroo
                            |
                            638
                            posts
                            |
                            
                        </span>
                        <span id="id_post_5510_created_date">
                            July 22, 2013, 2:12 p.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5510">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div id="id_post_5515" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5515_content_text" class="span8">
                        <p>Face slap.</p>
<p>Just parsed through my script and saw that I was purging the web2py database metadata as part of a db reset. This caused MySQL to fall over with the 'lost connection' error.</p>
<p>Apologies.</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5515_creator_details">
                            
                                
                                    <img src="/static/anywhere/images/star.gif" alt="Beloved premium user" title="Beloved premium user"/>
                                
                            
                            hurlingstats
                            |
                            7
                            posts
                            |
                            
                        </span>
                        <span id="id_post_5515_created_date">
                            July 22, 2013, 3:29 p.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5515">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div id="id_post_5914" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5914_content_text" class="span8">
                        <p>Just an FYI.</p>
<p>I've recreated this issue and my original explanation (that it was caused by accidentally purging the web2py db metadata) is wrong.</p>
<p>As described, the issue occurs when I run a long-running build script (creating schema, loading from csv files, moving data into new tables etc.)</p>
<p>The fix is to chunk the script in smaller more discrete components as suggested by Harry above.</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5914_creator_details">
                            
                                
                                    <img src="/static/anywhere/images/star.gif" alt="Beloved premium user" title="Beloved premium user"/>
                                
                            
                            hurlingstats
                            |
                            7
                            posts
                            |
                            
                        </span>
                        <span id="id_post_5914_created_date">
                            Aug. 15, 2013, 12:28 p.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5914">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div id="id_post_5917" class="span8 offset3 post">
                <div class="row">
                    <div id="id_post_5917_content_text" class="span8">
                        <p>Thanks for letting us know, hurlingstats.  How long is "long-running"?  Perhaps there's something we can tweak.</p>
                    </div>
                </div>
                <div class="row">
                    <div class="span7 post_metadata">
                        <span id="id_post_5917_creator_details">
                            
                                <img src="/static/anywhere/images/staff.png" alt="Staff" title="Staff"/>
                            
                            giles
                            |
                            866
                            posts
                            |
                            
                                PythonAnywhere staff
                                |
                            
                        </span>
                        <span id="id_post_5917_created_date">
                            Aug. 15, 2013, 2:27 p.m.
                        </span>
                        |
                        <a href="https://www.pythonanywhere.com/forums/topic/774/#id_post_5917">permalink</a>
                    </div>
                    <div class="span1">
                        
                    </div>
                </div>
            </div>
        </div>
    

    




        <div id="id_footer" class="container">

            <footer id="id_copyright_div">
                <p>
                    Copyright &copy; 2013 PythonAnywhere LLP
                    &mdash;
                    <a href="/terms/">Terms</a>
                    &mdash;
                    <a href="/privacy/">Privacy</a><br/>
                    "Python" is a registered trademark of the Python Software Foundation.
                </p>
            </footer>

        </div>

        
        

        <div id="id_feedback_dialog" title="Help us improve" style="display:none">
    <div id="id_feedback_dialog_blurb_big" class="dialog_blurb_big">
        It's always a pleasure to hear from you!
    </div>
    <div id="id_feedback_dialog_blurb_small">
        Ask us a question, or tell us what you love or hate about PythonAnywhere.<br/>
        We'll get back to you over email ASAP.
    </div>
    <textarea id="id_feedback_dialog_text" rows="6"></textarea>
    <input id="id_feedback_dialog_email_address" type="text" class="default_prompt"/>
    <div id="id_feedback_dialog_error" class="pa_hidden">
        Sorry, there was an error connecting to the server. <br/>Please try again in a few moments...
    </div>
    <div class="dialog_buttons">
        <img id="id_feedback_dialog_spinner" src="/static/anywhere/images/spinner-small.gif" />
        <button class="btn btn-primary" id="id_feedback_dialog_ok_button">OK</button>
        <button class="btn" id="id_feedback_dialog_cancel_button">Cancel</button>
    </div>
</div>


        <!-- Le javascript
        ================================================== -->
        <!-- Placed at the end of the document so the pages load faster -->
        <script type="text/javascript" src="/static/jquery/jquery-1.7.1.min.js"></script>
        <script type="text/javascript" src="/static/jquery/jquery-ui-1.8.11.custom.min.js"></script>
        <script src="/static/bootstrap/js/bootstrap-transition.js"></script>
        <script src="/static/bootstrap/js/bootstrap-alert.js"></script>
        <script src="/static/bootstrap/js/bootstrap-modal.js"></script>
        <script src="/static/bootstrap/js/bootstrap-dropdown.js"></script>
        <script src="/static/bootstrap/js/bootstrap-scrollspy.js"></script>
        <script src="/static/bootstrap/js/bootstrap-tab.js"></script>
        <script src="/static/bootstrap/js/bootstrap-tooltip.js"></script>
        <script src="/static/bootstrap/js/bootstrap-popover.js"></script>
        <script src="/static/bootstrap/js/bootstrap-button.js"></script>
        <script src="/static/bootstrap/js/bootstrap-collapse.js"></script>
        <script src="/static/bootstrap/js/bootstrap-carousel.js"></script>
        <script src="/static/bootstrap/js/bootstrap-typeahead.js"></script>

        <script type="text/javascript">
            var urls = {};
            var Anywhere = {};
            Anywhere.urls = {};
        </script>
        <script type="text/javascript" src="/static/anywhere/scripts/ajax_utils.js"></script>

        <script type="text/javascript" src="/static/anywhere/scripts/feedback_dialog.js"></script>
        
        <script type="text/javascript">
            $(function() {
                $.extend(
                    urls,
                    {
                        feedback: '/feedback/submit/'
                    }
                );
                
                    Anywhere.FeedbackDialog.Initialise(urls, true);
                
            });
        </script>

        <script type="text/javascript">

            var _gaq = _gaq || [];
            _gaq.push(['_setAccount', 'UA-18014859-6']);
            _gaq.push(['_trackPageview']);

            (function() {
                var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
                ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
                var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
            })();

        </script>

        



    </body>
</html>
